﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MLSMooc.BO;
using MLSMooc.Helper;
using System.Data.SqlClient;
using Dapper;
using MLSMooc.DAO.Interface.Course;

namespace MLSMooc.DAO.Implement.Course
{
   public class CourseDAO : ICourseDAO
    {
        private static CourseDAO instance = new CourseDAO();
        private CourseDAO() { }
        public static CourseDAO Getinstance() => instance;

        private static readonly string queryField = "select c.*,p.Pro_Name as PName,t.TName,t.Company,t.Position,t.Section  from Mooc_Course c join Mooc_Project p on c.ProId=p.Id left join Mooc_Teach t on c.TeachId=t.Id where c.isDelete=0 and p.isDelete=0";

        public async Task<CourseBO> GetByIdCourseInfo(int id)
        {
            using (SqlConnection conn=new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder(queryField);
                sql.Append($@" and c.Id=@id");
                var res = await conn.QueryAsync<CourseBO>(sql.ToString(), new { id = id });
                return res.FirstOrDefault();
            }
        }

        public async Task<IEnumerable<CourseBO>> GetCourseList()
        {
            using (SqlConnection  conn=new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder(queryField);
                var res = await conn.QueryAsync<CourseBO>(sql.ToString());
                return res;
            }
        }

        public async Task<IEnumerable<CourseBO>> GetCourseVideoList()
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder(queryField);
                sql.Append($@" and c.Course_VideoUrl<>'' order by c.ProId,c.Sort");
                var res = await conn.QueryAsync<CourseBO>(sql.ToString());
                return res;
            }
        }

        public async Task<IEnumerable<CourseBO>> GetByProIdCourseList(int ProId)
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder(queryField);
                sql.Append($@" and c.ProId=@id");
                var res = await conn.QueryAsync<CourseBO>(sql.ToString(),new { id=ProId});
                return res;
            }
        }

        public async Task<IEnumerable<ExamBO>> GetExam(int id)
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder($@"select *,(select count(*) from Mooc_Exam where CourseId=@Id and TopicType=1 and isDelete=0) t1 , (select count(*) from Mooc_Exam where CourseId=@Id and TopicType=2 and isDelete=0) t2 ,(select count(*) from Mooc_Exam where CourseId=@Id and TopicType=3 and isDelete=0) t3,(select count(*) from Mooc_Exam where CourseId=@Id and TopicType=4 and isDelete=0) t4 from Mooc_Exam where isDelete=0 and CourseId=@Id  order by TopicType");
                var res = await conn.QueryAsync<ExamBO>(sql.ToString(), new { Id = id });
                return res;
            }
        }
        public async Task<IEnumerable<ExamBO>> GetMoocTankExam(int id)
        {
            using (SqlConnection conn = new SqlConnection(SalesSysSqlHelper.connectionString))
            {
                StringBuilder sql = new StringBuilder($@"select  top 1 * from Mooc_ExamNew where isDelete=0 and CourseId=@Cid and TopicType=1 order by TopicType");
                var res = await conn.QueryAsync<ExamBO>(sql.ToString(), new { Cid = id });
                return res;
            }
        }
    }
}
